package data.dao.daoImplementation;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import data.dao.UserHierarchyDAOException;
import data.dao.UserHierarchyDetailDAO;
import data.dto.UserHierarchyDTO;
import data.dto.UserHierarchyDetailDTO;
import data.searchdto.UserHierarchyDetailSearchDTO;

public class UserHierarchyDetailDAOImpl implements UserHierarchyDetailDAO {

	@Override
	public UserHierarchyDetailDTO findHierarchyDetail(
			UserHierarchyDetailSearchDTO hierarchydetailsearchdto)
			throws UserHierarchyDAOException {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM hierarchydetail WHERE usertype = ? AND hierarchyid = ?";
		UserHierarchyDetailDTO userHierarchyDetail = null;

		try {
			PreparedStatement smt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			smt.setShort(1, hierarchydetailsearchdto.getUserType());
			smt.setShort(2, hierarchydetailsearchdto.getHierarchyId());
			
			ResultSet result = util.Database.getInstance().executeQuery(smt);

			if (result.next()) {
				userHierarchyDetail = getUserHierarchyDetailDTO(result);
			}

			smt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return userHierarchyDetail;
	}

	@Override
	public ArrayList<UserHierarchyDetailDTO> findAllHierarchyDetail(
			UserHierarchyDetailSearchDTO userHierarchyDetailSearch)
			throws UserHierarchyDAOException {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM hierarchydetail WHERE hierarchyid = ?";
		ArrayList<UserHierarchyDetailDTO> userHierarchyDetails = new ArrayList<UserHierarchyDetailDTO>();

		try {
			PreparedStatement smt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			smt.setShort(1, userHierarchyDetailSearch.getHierarchyId());

			ResultSet result = util.Database.getInstance().executeQuery(smt);

			while (result.next()) {
				userHierarchyDetails.add(getUserHierarchyDetailDTO(result));
			}

			smt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return userHierarchyDetails;
	}

	@Override
	public void insertHierarchyDetail(UserHierarchyDetailDTO hierarchydetail)
			throws UserHierarchyDAOException {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO hierarchydetail (hierarchyid, usertype) VALUES(?, ?)";

		try {
			PreparedStatement smt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			smt.setInt(1, hierarchydetail.getHierarchyId());
			smt.setShort(2, hierarchydetail.getUserType());

			int rowcount = util.Database.getInstance().executeUpdate(smt);
			if (rowcount != 1) {
				// System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

			smt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void updateHierarchyDetail(UserHierarchyDetailDTO hierarchydetail)
			throws UserHierarchyDAOException {
		// TODO Auto-generated method stub
		String sql = "UPDATE hierarchy SET hierarchyid = ?, usertype = ? where id = ?";

		try {
			PreparedStatement smt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			smt.setInt(1, hierarchydetail.getId());
			smt.setShort(3, hierarchydetail.getHierarchyId());
			smt.setShort(2, hierarchydetail.getUserType());

			int rowcount = util.Database.getInstance().executeUpdate(smt);
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
			smt.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void deleteHierarchyDetail(UserHierarchyDetailDTO hierarchydetail)
			throws UserHierarchyDAOException, NotFoundException {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM hierarchydetail WHERE hierarchyid = ? AND usertype = ?";

		try {
			PreparedStatement smt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			smt.setShort(1, hierarchydetail.getHierarchyId());
			smt.setInt(2, hierarchydetail.getUserType());

			int rowcount = util.Database.getInstance().executeUpdate(smt);
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
			smt.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private UserHierarchyDetailDTO getUserHierarchyDetailDTO(ResultSet rs) {
		UserHierarchyDetailDTO userHierarchyDetail = null;
		try {
			userHierarchyDetail = new UserHierarchyDetailDTO(rs.getShort("id"),
					rs.getShort("hierarchyid"), rs.getShort("usertype"));
		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return userHierarchyDetail;
	}
}
